﻿using Dapper;
using Lifang.Entites;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Lifang.Dao
{
    public class DaoHelper<T>
    {
        public string ConnStr = "Server=47.98.187.80;uid=root;pwd=zywmg121603!@#;database=LifangDB;SslMode = none;"; 
        

        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Add<T>(T t) where T:BaseEntity,new()
        {            
            using (MySqlConnection connection = new MySqlConnection(ConnStr))
            {
                string insertSql = GetInsertSql(t);
                insertSql += ";select last_insert_id();";
                var id = connection.QueryFirst<int>(insertSql);
                return id;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strsql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Update<T>(string strsql,object param) where T:BaseEntity,new()
        {
            using (MySqlConnection connection = new MySqlConnection(ConnStr))
            {                
                return connection.Execute(strsql, param);
            }
        }


        #region 查询相关操作
            /// <summary>
            /// 查询所有用户信息
            /// </summary>
            /// <returns></returns>
        public List<T> GetAll(string sql)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnStr))
            {                
                var query = connection.Query<T>(sql);
                return query.ToList();
            }
        }
        #endregion

        #region 查询单个信息
        /// <summary>
        /// 查询单个用户的信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public T GetModel<T>(int id) where T: BaseEntity, new()
        {
            T t = new T();
            string strsql = string.Format("SELECT * FROM {0} WHERE Id=@Id", t.DbName + "." + t.TableName);
            using (MySqlConnection connection = new MySqlConnection(ConnStr))
            {
                var query = connection.Query<T>(strsql, new { id }).SingleOrDefault();
                return query;
            }
        }
        #endregion 

        #region 分页
        /// <summary>
        /// 用户信息分页查询接口
        /// </summary>
        /// <param name="PageNumber">要查询的第几页</param>
        /// <param name="PageSize">每页要显示几行数据</param>
        /// <param name="TotalItemCount">返回的总条数</param>
        /// <param name="TotalPageCount">返回的总页数</param>
        /// <returns></returns>
        public List<T> GetListByPage<T>(int PageNumber, int PageSize, out int TotalItemCount, out int TotalPageCount) where T : BaseEntity, new()
        {
            T t = new T();
            int pagestart = 0;
            TotalItemCount = 0;
            TotalPageCount = 0;
            pagestart = (PageNumber - 1) * PageSize;
            StringBuilder Sqlstr1 = new StringBuilder();
            Sqlstr1.AppendFormat("select Count(0) FROM {0};",t.DbName+"."+t.TableName);
            using (MySqlConnection connection = new MySqlConnection(ConnStr))
            {
                TotalItemCount = connection.ExecuteScalar<int>(Sqlstr1.ToString());
            }
            TotalPageCount = TotalItemCount / PageSize + 1;

            StringBuilder Sqlstr2 = new StringBuilder();
            Sqlstr2.AppendFormat($"select * from {0} limit { pagestart },{ PageSize};", t.DbName + "." + t.TableName);
            using (MySqlConnection connection = new MySqlConnection(ConnStr))
            {
                var result = connection.Query<T>(Sqlstr2.ToString());
                if (result.Count() > 0 && result != null)
                {
                    return result.ToList();
                }
                return null;
            }
        }
        #endregion


        private string GetInsertSql<T>(T obj)where T: BaseEntity, new()
        {
            string fileds = null;
            string values = null;            
            Type type = obj.GetType();            
            //获取所有公有属性
            PropertyInfo[] info = type.GetProperties();
            foreach (PropertyInfo var in info)
            {
                //取得属性的特性标签，false表示不获取因为继承而得到的标签
                Object[] attr = var.GetCustomAttributes(false);
                if (attr.Length > 0)
                {
                    //从注解数组中取第一个注解(一个属性可以包含多个注解)
                    MyAttribute myattr = attr[0] as MyAttribute;
                    if (myattr.PrimaryKey || myattr.Ignore)
                    {
                        continue;
                    }
                }
                if(var.CanWrite)
                {
                    fileds += var.Name + ",";
                    values += "'" + var.GetValue(obj, null) + "',";
                }
                
            }
            fileds = fileds.Substring(0, fileds.Length - 1);
            values = values.Substring(0, values.Length - 1);
            string insertSql = "INSERT INTO {0}({1}) VALUES ({2})";
            insertSql = string.Format(insertSql, obj.DbName+"."+obj.TableName, fileds, values);
            return insertSql;
        }

        private string GetUpdateSql<T>(T obj) where T : BaseEntity, new()
        {
            Type type = obj.GetType();
            //获取所有公有属性
            PropertyInfo[] info = type.GetProperties();
            foreach (PropertyInfo var in info)
            {
                //取得属性的特性标签，false表示不获取因为继承而得到的标签
                Object[] attr = var.GetCustomAttributes(false);
                if (attr.Length > 0)
                {
                    //从注解数组中取第一个注解(一个属性可以包含多个注解)
                    MyAttribute myattr = attr[0] as MyAttribute;
                    if (myattr.PrimaryKey || myattr.Ignore)
                    {
                        continue;
                    }
                }
            }
            string updateSQL = string.Empty;
            return updateSQL;
        }
    }
}
